package com.blackcat.recode.dao;

import com.blackcat.recode.entity.Branch;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
/**
 * <p> 分支信息表 Mapper 接口
 * @author blackcat 2024-09-10
 */
public interface BranchMapper extends BaseMapper<Branch> {


//    @Select("SELECT \n" +
//            "    b.*,\n" +
//            "    MAX(ur.version_no) AS lastVersion,\n" +
//            "    DATE_FORMAT(MAX(CASE WHEN ur.version_no = (SELECT MAX(version_no) FROM update_recode WHERE branch_id = b.id) THEN ur.create_time END), '%Y-%m-%d') AS lastDate\n" +
//            "FROM branch b\n" +
//            "JOIN update_recode ur ON b.id = ur.branch_id\n" +
//            "WHERE b.service_id = #{serviceId}\n" +
//            "GROUP BY b.id, b.branch_name")

   @Select("SELECT b.*,\n" +
           "    SUBSTRING_INDEX(\n" +
           "        GROUP_CONCAT(ur.version_no ORDER BY \n" +
           "            CAST(SUBSTRING_INDEX(ur.version_no, '.', 1) AS UNSIGNED) * 10000 +\n" +
           "            CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(ur.version_no, '.', 2), '.', -1) AS UNSIGNED) * 100 +\n" +
           "            CAST(SUBSTRING_INDEX(ur.version_no, '.', -1) AS UNSIGNED) DESC\n" +
           "        ), \n" +
           "        ',', 1\n" +
           "    ) AS lastVersion,\n" +
           "    DATE_FORMAT(\n" +
           "        SUBSTRING_INDEX(\n" +
           "            GROUP_CONCAT(ur.create_time ORDER BY \n" +
           "                CAST(SUBSTRING_INDEX(ur.version_no, '.', 1) AS UNSIGNED) * 10000 +\n" +
           "                CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(ur.version_no, '.', 2), '.', -1) AS UNSIGNED) * 100 +\n" +
           "                CAST(SUBSTRING_INDEX(ur.version_no, '.', -1) AS UNSIGNED) DESC\n" +
           "            ), \n" +
           "            ',', 1\n" +
           "        ), \n" +
           "        '%Y-%m-%d'\n" +
           "    ) AS lastDate\n" +
           "FROM branch b\n" +
           "JOIN update_recode ur ON b.id = ur.branch_id\n" +
           "WHERE b.service_id = #{serviceId}\n" +
           "GROUP BY b.id, b.branch_name")
    List<Branch> selectAllList(String serviceId);
}
